from odoo import fields, models, _, api


class FixtureProductReport(models.Model):
    _name = "fixture.product.report"
    _description = "Fixture Product Report"
    _auto = False

    customer_code = fields.Char(string='Customer Code')
    customer_name = fields.Char(string='Customer Name')
    product_name = fields.Char(string='Product Name')
    total_s_amount_total_usd = fields.Float(string='Total Sales Amount')
    total_nre = fields.Float(string='Total NRE')
    sale_currency = fields.Char(string='Sale Currency')
    default_code = fields.Char(string='Product Code')
    supplier_product_name = fields.Char(string='Supplier Product Name')
    price_thb = fields.Float(string='Price THB')
    price_usd = fields.Float(string='Price USD')
    proportion_cost = fields.Integer(string='Proportion Cost')
    proportion_rate = fields.Integer(string='Proportion Rate')
    first_incoming_date = fields.Date(string='First Incoming Date')

    @property
    def _table_query(self):
        query = """
        WITH ProductSales AS (
            SELECT
                pt.id AS product_template_id,
                pt.name AS product_name,
                (regexp_match(pt.name, 'L(\d+)[A-Z]')) AS extracted_sales_code,
                rp.name AS customer_name,
                rp.code AS customer_code,
                SUM(sil.s_amount_total_usd) AS total_s_amount_total_usd,
                SUM(COALESCE(sil.NRE, 0)) AS total_nre,
                res_currency.name AS sale_currency
            FROM ship_instructions_line sil
            LEFT JOIN sale_order_line sol 
                ON sil.sale_order_line_id = sol.id
            LEFT JOIN sale_order so 
                ON sol.order_id = so.id
            LEFT JOIN res_partner rp 
                ON so.partner_id = rp.id
            LEFT JOIN product_product pp 
                ON sol.product_id = pp.id
            LEFT JOIN product_template pt 
                ON pp.product_tmpl_id = pt.id
            LEFT JOIN res_currency 
                ON sil.s_currency_id_usd = res_currency.id
            WHERE sil.s_amount_total_usd IS NOT NULL
            GROUP BY 
                pt.id, 
                pt.name, 
                res_currency.name, 
                rp.name, 
                rp.code
        ),
        ProductSupplierInfo AS (
            SELECT DISTINCT ON (extracted_supplier_code)
                pt.id AS product_template_id,
                pt.default_code,
                pt.name AS supplier_product_name,
                (regexp_match(pt.default_code, 'FXET(\d+)')) AS extracted_supplier_code,
                psi.price AS price_thb,   -- 原始泰铢价格
                ROUND(psi.price / 35, 2) AS price_usd,  -- 按1:35转换为美元
                res_currency.name AS currency_name
            FROM product_supplierinfo psi
            LEFT JOIN product_template pt 
                ON psi.product_tmpl_id = pt.id
            LEFT JOIN res_currency 
                ON psi.currency_id = res_currency.id
            WHERE pt.categ_id = 381
            ORDER BY 
                extracted_supplier_code, 
                psi.price DESC
        ),
        ProductStockMoves AS (
            SELECT
                pp.product_tmpl_id AS product_template_id,
                MIN(sm.date) AS first_incoming_date  -- 最早入库时间
            FROM stock_move sm
            JOIN product_product pp ON sm.product_id = pp.id
            JOIN stock_picking_type spt ON sm.picking_type_id = spt.id
            WHERE
                sm.state = 'done'  -- 仅完成状态的移动
                AND spt.code = 'incoming'  -- 仅入库类型
            GROUP BY pp.product_tmpl_id
        )
        SELECT
            ROW_NUMBER() OVER () AS id,
            ps.customer_code,
            ps.customer_name,
            ps.product_name,
            ps.total_s_amount_total_usd,
            ps.total_nre,
            ps.sale_currency,
            psi.default_code,
            psm.first_incoming_date,
            psi.supplier_product_name,
            psi.price_thb,      -- 泰铢原价
            psi.price_usd,      -- 转换后的美元价格
            -- 不包含NRE的费用占比（保留两位小数百分比）
            (psi.price_usd / NULLIF((ps.total_s_amount_total_usd - ps.total_nre), 0)) AS proportion_cost,
            -- 总费用占比（保留两位小数百分比）
            (psi.price_usd / NULLIF(ps.total_s_amount_total_usd, 0)) AS proportion_rate
        FROM
            ProductSales ps
        LEFT JOIN
            ProductSupplierInfo psi 
            ON ps.extracted_sales_code = psi.extracted_supplier_code
        LEFT JOIN ProductStockMoves psm 
            ON psi.product_template_id = psm.product_template_id
        where psi.default_code is not null
        ORDER BY 
            ps.customer_code,
            ps.customer_name, 
            ps.product_name
        """
        return query
